﻿using OfficeOpenXml;
using OfficeOpenXml.Style;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Text.RegularExpressions;
using static OfficeOpenXml.ExcelWorksheet;

namespace WAU.Utils
{
    /// <summary>
    /// Excel 工具类（使用 EPPlus 库）
    /// </summary>
    public class ExcelEPPlusUtils
    {
        /// <summary>
        /// 列名和宽度系数类
        /// </summary>
        public class ColumnValueWidth
        {
            /// <summary>
            /// 列名
            /// </summary>
            public string Name { get; set; }
            /// <summary>
            /// 宽度系数
            /// </summary>
            public double WidthFactor { get; set; } = 1;
        }

        /// <summary>
        /// DateTime 类型格式化 特性
        /// </summary>
        public class DateTimeFormatAttribute : Attribute
        {
            /// <summary>
            /// 格式化类型字符串
            /// </summary>
            public string FormatString { get; set; }

            /// <summary>
            /// ctor
            /// </summary>
            /// <param name="formatString"></param>
            public DateTimeFormatAttribute(string formatString)
            {
                this.FormatString = formatString;
            }
        }

        #region 导出相关

        /// <summary>
        /// 要导入的项（对一个 sheet 的设置）
        /// </summary>
        /// <typeparam name="T"></typeparam>
        public class ToExportItem
        {
            /// <summary>
            /// 数据的类型
            /// </summary>
            public Type DataType { get; set; }
            /// <summary>
            /// 列（可以有多行）
            /// </summary>
            public List<Dictionary<string, ColumnValueWidth>> Dics { get; set; }
            /// <summary>
            /// 数据集合
            /// </summary>
            public List<object> DataList { get; set; }
            /// <summary>
            /// sheet 的名称
            /// </summary>
            public string SheetName { get; set; }
            /// <summary>
            /// 处理数据的委托
            /// </summary>
            public Func<PropertyInfo, object, ExcelRange, bool> DataHandler { get; set; }
            /// <summary>
            /// 全部处理完成后的委托
            /// </summary>
            public Func<ExcelWorksheet, bool> FinishHandler { get; set; }
            /// <summary>
            /// 是否验证 列 必须被 数据项 包含
            /// </summary>
            public bool IsNeedValidate { get; set; } = true;
        }

        /// <summary>
        /// 导出 Excel
        /// </summary>
        /// <param name="newFileFullName"></param>
        /// <param name="toExportItems"></param>
        public static void Export(string newFileFullName, params ToExportItem[] toExportItems)
        {
            using (ExcelPackage package = new ExcelPackage(new FileInfo(newFileFullName)))
            {
                for (int itemIndex = 0; itemIndex < toExportItems.Length; itemIndex++)
                {
                    ToExportItem item = toExportItems[itemIndex];

                    Dictionary<string, ColumnValueWidth> indeedDic = item.Dics.Last();

                    PropertyInfo[] typeProperties = item.DataType.GetProperties();
                    if (item.IsNeedValidate)
                    {
                        ValidProperties(typeProperties, indeedDic);
                    }

                    // 通过内连接达到 排序 和 删除不必要字段 的功能
                    typeProperties = indeedDic.Join(typeProperties, a => a.Key, b => b.Name, (a, b) => b).ToArray();

                    int rowNumber = 1;

                    if (string.IsNullOrEmpty(item.SheetName))
                    {
                        item.SheetName = string.Format("sheet{0}", itemIndex);
                    }

                    ExcelWorksheet worksheet = package.Workbook.Worksheets.Add(item.SheetName);     //创建worksheet

                    // 画所有标题行
                    foreach (var dic in item.Dics)
                    {
                        for (int dicIndex = 0; dicIndex < dic.Count(); dicIndex++)
                        {
                            worksheet.Cells[rowNumber, dicIndex + 1].Value = dic.ElementAt(dicIndex).Value.Name;
                            worksheet.Column(dicIndex + 1).Width = dic.ElementAt(dicIndex).Value.WidthFactor * 20;
                            worksheet.Cells[rowNumber, dicIndex + 1].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
                            worksheet.Cells[rowNumber, dicIndex + 1].Style.Font.Bold = true;
                            worksheet.Cells[rowNumber, dicIndex + 1].Style.Border.BorderAround(ExcelBorderStyle.Thin);
                        }
                        rowNumber += 1;
                    }

                    foreach (var data in item.DataList)
                    {
                        for (int dicKeyIndex = 0; dicKeyIndex < indeedDic.Count; dicKeyIndex++)
                        {
                            PropertyInfo pi = typeProperties[dicKeyIndex];

                            if (pi != null)
                            {
                                object value = pi.GetValue(data);

                                if (item.DataHandler == null || !item.DataHandler(pi, value, worksheet.Cells[rowNumber, dicKeyIndex + 1]))
                                {
                                    DataEssentialHandler(pi, value, worksheet.Cells[rowNumber, dicKeyIndex + 1]);
                                }
                            }
                            else
                            {
                                NullDataHandler(worksheet.Cells[rowNumber, dicKeyIndex + 1]);
                            }

                            worksheet.Cells[rowNumber, dicKeyIndex + 1].Style.Border.BorderAround(ExcelBorderStyle.Thin);
                        }
                        rowNumber += 1;
                    }

                    if (item.FinishHandler != null)
                    {
                        item.FinishHandler(worksheet);
                    }

                    package.Save();
                }
            }
        }

        /// <summary>
        /// 验证属性是否有未匹配的属性
        /// </summary>
        /// <returns></returns>
        private static void ValidProperties(PropertyInfo[] typeProperties, Dictionary<string, ColumnValueWidth> dic)
        {
            List<string> typePropertyNames = typeProperties.Select(tp => tp.Name).ToList();
            List<string> sheetColumnNames = dic.Select(a => a.Key).ToList();

            IEnumerable<string> unexpectedProperties = sheetColumnNames.Except(typePropertyNames);
            if (unexpectedProperties.Any())
            {
                throw new Exception(string.Format("列 {0} 不在源数据的属性中", string.Join("，", unexpectedProperties)));
            }
        }

        /// <summary>
        /// 基本的数据处理逻辑
        /// </summary>
        /// <param name="worksheet"></param>
        /// <param name="keyName"></param>
        /// <param name="pi"></param>
        /// <param name="value"></param>
        /// <param name="cell"></param>
        public static void DataEssentialHandler(PropertyInfo pi, Object value, ExcelRange cell)
        {
            Type pt = pi.PropertyType;

            if (value == null)
            {
                cell.Value = string.Empty;
                return;
            }

            if (pt == typeof(DateTime) || pt == typeof(DateTime?))
            {
                DateTimeFormatAttribute att = pi.GetCustomAttribute<DateTimeFormatAttribute>();
                string tmpFormatString;
                if (att == null)
                {
                    tmpFormatString = "yyyy-MM-dd HH:mm:ss";
                }
                else
                {
                    tmpFormatString = att.FormatString;
                }

                cell.Value = ((DateTime)value).ToString(tmpFormatString);
            }
            else
            {
                cell.Value = value;
            }

            cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
            cell.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
        }

        /// <summary>
        /// 空数据的基本处理逻辑
        /// </summary>
        /// <param name="worksheet"></param>
        /// <param name="keyName"></param>
        /// <param name="pi"></param>
        /// <param name="value"></param>
        /// <param name="cell"></param>
        public static void NullDataHandler(ExcelRange cell)
        {
            cell.Value = string.Empty;
            cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
            cell.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
        }

        #endregion

        #region 导入相关

        public class ExcelImportResult<T>
        {
            public List<T> DataList { get; set; }
            public string Msg { get; set; }
        }

        /// <summary>
        /// 将 Excel 中的数据转换为 List 类型
        /// </summary>
        /// <typeparam name="T">必须无参构造函数的类型</typeparam>
        /// <param name="excelFilePath"></param>
        /// <param name="dic"></param>
        /// <param name="startRowNumber"></param>
        /// <param name="convertValueMethod"></param>
        /// <param name="error"></param>
        /// <param name="sheetNum"></param>
        /// <returns></returns>
        public static ExcelImportResult<T> Import<T>(string excelFilePath, int startRowNumber = 2,
            Func<PropertyInfo, object, string, object> convertValueMethod = null, int sheetNum = 0)
            where T : new()
        {
            List<T> resultList = new List<T>();
            PropertyInfo[] propertyInfos = typeof(T).GetProperties();
            var dic = propertyInfos.Select(a =>
            {
                var attribute = a.GetCustomAttribute<ExcelColumnAttribute>();

                return new
                {
                    PropertyInfo = a,
                    ColumnName = attribute?.ColumnName,
                    IsRequire = attribute != null && attribute.IsRequire
                };

            }).Where(a => a.ColumnName != null).ToList();

            using (ExcelPackage package = new ExcelPackage(new FileInfo(excelFilePath)))
            {
                ExcelWorksheet worksheet = package.Workbook.Worksheets[sheetNum];

                HandleMergedColumns(worksheet);

                List<string> titles = new List<string>();

                int rowCount = worksheet.Dimension.Rows;
                int colCount = worksheet.Dimension.Columns;

                for (int colIndex = 1; colIndex <= colCount; colIndex++)
                {
                    titles.Add(worksheet.Cells[1, colIndex].Value.ToString());
                }
                dic = titles.Join(dic, a => a, b => b.ColumnName, (a, b) => b).ToList();

                for (int rowIndex = startRowNumber; rowIndex <= rowCount; rowIndex++)
                {
                    T obj = new T();

                    // 如果整行数据都为空，则跳过
                    bool isThisRowEmpty = true; 
                    for(var colIndex = 0; colIndex < colCount; colIndex++)
                    {
                        if(worksheet.Cells[rowIndex, colIndex + 1].Value == null)
                        {
                            isThisRowEmpty = false;
                            break;
                        }
                    }
                    if (isThisRowEmpty)
                    {
                        continue;
                    }

                    // 读取数据
                    for (int dicIndex = 0; dicIndex < dic.Count; dicIndex++)
                    {
                        PropertyInfo propertyInfo = dic[dicIndex].PropertyInfo;
                        object cellValue = worksheet.Cells[rowIndex, dicIndex + 1].Value;

                        if(cellValue == null)
                        {
                            if (dic[dicIndex].IsRequire)
                            {
                                return new ExcelImportResult<T> { Msg = $"第 { rowIndex } 行的 { dic[dicIndex].ColumnName } 字段不能为空" };
                            }
                            else
                            {
                                continue;
                            }                            
                        }

                        string value = cellValue.ToString();
                        if (propertyInfo.PropertyType == typeof(string))
                        {
                            propertyInfo.SetValue(obj, value);
                        }
                        else
                        {
                            object tmpValue;
                            if (convertValueMethod == null)
                            {
                                tmpValue = EssentialConvertValueTypeMethod(propertyInfo, obj, value);
                            }
                            else
                            {
                                tmpValue = convertValueMethod(propertyInfo, obj, value);
                            }

                            propertyInfo.SetValue(obj, tmpValue);
                        }
                    }

                    resultList.Add(obj);
                }
            }

            return new ExcelImportResult<T> { DataList = resultList };
        }

        /// <summary>
        /// 单元格地址（如 A2，Z8） 正则
        /// </summary>
        private static Regex CellAddressRegex = new Regex("([a-zA-Z]+)(\\d+)");

        /// <summary>
        /// 处理合并的区域，给这个区域中的所有单元格赋同一个值
        /// </summary>
        /// <param name="worksheet"></param>
        private static void HandleMergedColumns(ExcelWorksheet worksheet)
        {
            MergeCellsCollection mergeCells = worksheet.MergedCells;

            foreach (var mergeCell in mergeCells)
            {
                string[] tmpFromTo = mergeCell.Split(":");
                string from = tmpFromTo[0], to = tmpFromTo[1];

                Match fromMatch = CellAddressRegex.Match(from);
                int fromRowNum = int.Parse(fromMatch.Groups[2].Value);
                int fromColNum = ConvertLetterAddressToNumber(fromMatch.Groups[1].Value);

                Match toMatch = CellAddressRegex.Match(to);
                int toRowNum = int.Parse(toMatch.Groups[2].Value);
                int toColNum = ConvertLetterAddressToNumber(toMatch.Groups[1].Value);

                string value = worksheet.Cells[fromRowNum, fromColNum].Value?.ToString();

                for (int i = fromRowNum; i <= toRowNum; i++)
                {
                    for (int j = fromColNum; j <= toColNum; j++)
                    {
                        worksheet.Cells[i, j].Value = value;
                    }
                }
            }
        }

        /// <summary>
        /// 将字母地址转为数字地址
        /// </summary>
        /// <param name="letterAddress"></param>
        /// <returns></returns>
        private static int ConvertLetterAddressToNumber(string letterAddress)
        {
            double result = 0;

            // 相当于将 二十六进制 转为 十进制
            string tmp = new string(letterAddress.ToUpper().Reverse().ToArray());
            for (var i = 0; i < tmp.Length; i++)
            {
                result += ((char)tmp[i] - 65 + 1) * Math.Pow(26, i);
            }
            return (int)result;
        }

        /// <summary>
        /// 基本的 字符串类型值 转换为 目标类型值 的方法（尝试调用 Parse）
        /// </summary>
        /// <param name="propertyInfo"></param>
        /// <param name="obj"></param>
        /// <param name="value"></param>
        /// <returns></returns>
        public static object EssentialConvertValueTypeMethod(PropertyInfo propertyInfo, Object obj, string value)
        {
            Type propertyType = propertyInfo.PropertyType;
            if (propertyType.IsGenericType
                && propertyType.GetGenericTypeDefinition().Equals(typeof(Nullable<>)))
            {
                propertyType = propertyType.GenericTypeArguments[0];
            }
            MethodInfo mi = propertyType.GetMethod("Parse", new Type[] { typeof(string) });
            if (mi != null)
            {
                return mi.Invoke(obj, new object[] { value });
            }
            else
            {
                throw new Exception("目标类型中不包含 Parse 方法，请传入 convertValueMethod 参数以使用合适的方法转换值");
            }
        }

        #endregion

        #region 通用

        /// <summary>
        /// 判断是否为 Excel 文件
        /// </summary>
        /// <param name="fileFullName"></param>
        /// <returns></returns>
        public static bool IsExcel(string fileFullName)
        {
            // 需要单独判断文件是否存在（下面的代码文件不存在时不会报错）
            FileInfo fileInfo = new FileInfo(fileFullName);
            if (!fileInfo.Exists)
            {
                return false;
            }

            try
            {
                using (ExcelPackage package = new ExcelPackage(new FileInfo(fileFullName)))
                {
                    _ = package.Workbook.Worksheets.Count();
                }
            }
            catch
            {
                return false;
            }

            return true;
        }

        [AttributeUsage(AttributeTargets.Property)]
        public class ExcelColumnAttribute : Attribute
        {
            public string ColumnName { get; set; }
            public bool IsRequire { get; set; }

            /// <summary>
            /// ctor
            /// </summary>
            /// <param name="columnName"></param>
            /// <param name="isRequire"></param>
            public ExcelColumnAttribute(string columnName, bool isRequire = true)
            {
                this.ColumnName = columnName;
                this.IsRequire = isRequire;
            }
        }

        #endregion
    }
}
